﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace VOEISHubPushServiceClient
{
    class SQLCommunicator
    {

        public DataTable ListSqlInstances()
        {
            // Retrieve the enumerator instance and then the data.
            SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;
            DataTable table = instance.GetDataSources();

            return table;
        }

        public DataTable ListSqlDatabases(String SqlInstance, String Security, String UserName, String Password)
        {
            //Retrieve the enumerator instance and then the data
            
            String strConnection = "";
            if (Security == "Windows")
            {
                strConnection = "Data Source=" + SqlInstance + ";Initial Catalog=master;Integrated Security=SSPI;";
            }
            else
            {
                strConnection = "Data Source=" + SqlInstance + ";Initial Catalog=master;User Id=" + UserName + ";Password=" + Password + ";";
            }

            SqlConnection conn = new SqlConnection(strConnection);
            SqlCommand cmd = new SqlCommand("SELECT name FROM sys.databases", conn);
            DataTable table = new DataTable();

            try
            {
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
                reader.Close();
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message.ToString());
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
                return table;
        }

        public String GetPushResults(String connectionString, Int32 SyncID)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("EXEC dbo.usp_GetPushResults " + SyncID, conn);
            DataTable table = new DataTable();
            String pushResults = "";

            try
            {
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    pushResults = reader[0].ToString();
                }
                
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message.ToString(), "Failed to retrieve push results.", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return pushResults;
        }

        public Boolean TestConnection(String connectionstring)
        {
            SqlConnection conn = new SqlConnection(connectionstring);
            SqlCommand cmd = new SqlCommand("SELECT 1 WHERE 1 = 1", conn);
            Boolean result = false;

            try
            {
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                reader.Close();
                result = true;
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message.ToString(), "Connection Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);    
                result = false;
            }
            finally
            {
                if (conn.State ==  ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return result;
            }

        public Dictionary<string, string> ListDataSets(String connectionstring)
        {
            Dictionary<string, string> dictionary = new Dictionary<string, string>();
            SqlConnection conn = new SqlConnection(connectionstring);
            SqlCommand cmd = new SqlCommand("EXEC dbo.usp_GetDataSets", conn);

            try
            {
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    dictionary.Add(reader[0].ToString(), reader[1].ToString());
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message.ToString(), "Dataset Enumeration Failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }

            }

            return dictionary;
        }

        public ArrayList ListProjectIDs(String connectionstring)
        {
            ArrayList projectIDs = new ArrayList();
            SqlConnection conn = new SqlConnection(connectionstring);
            SqlCommand cmd = new SqlCommand("EXEC dbo.usp_GetProjectIDs", conn);

            try
            {
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    projectIDs.Add(reader[0].ToString());
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message.ToString(), "Project ID Enumeration Failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return projectIDs;
        }
        }
    }